# models.py

from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
import pandas as pd

db = SQLAlchemy()


class Article(db.Model):
    __tablename__ = 'articles'
    id = db.Column(db.Integer, primary_key=True)
    feed_id = db.Column(db.Integer,default=False)
    title = db.Column(db.String(255), nullable=False)
    link = db.Column(db.Text, nullable=False)
    description = db.Column(db.Text, nullable=False)
    published_at = db.Column(db.DateTime)
    is_read = db.Column(db.Boolean, default=False)
    is_favorite = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    is_deleted = db.Column(db.Boolean, default=False)
    bookmark = db.Column(db.Boolean, default=False)
    # ...


class Category(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    chname = db.Column(db.String(50), nullable=False)
    is_deleted = db.Column(db.Boolean, default=False)

    def __repr__(self):
        return f'<Category id={self.id}, name={self.name}, created_at={self.created_at}, updated_at={self.updated_at}>'
    def format_category(self):
        return {
            'id': self.id,
            'name': self.name,
            'chname': self.chname,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S'),
            'is_deleted':self.is_deleted
        }

    def add_category(name, chname):
        now = datetime.utcnow()
        category = Category(name=name, chname=chname, created_at=now, updated_at=now, is_deleted=0)
        db.session.add(category)
        db.session.commit()
        return category


class RSSSource(db.Model):
    __tablename__ = 'rss_sources'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    url = db.Column(db.String(255), nullable=False)
    image = db.Column(db.String(255), nullable=False)
    description = db.Column(db.String(255), nullable=False)
    category_id = db.Column(db.Integer, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    is_deleted = db.Column(db.Boolean, default=False)

    def serialize(self):
        return {
            'id': self.id,
            'title': self.title,
            'url': self.url,
            'image': self.image,
            'description': self.description,
            'category_id': self.category_id,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S'),
            'is_deleted':self.is_deleted
        }

    def process_excel_data(file_name):
        # 读取Excel文件
        df = pd.read_excel(file_name)

        # 遍历每一行数据，并创建RSSSource对象
        rss_sources = []
        for index, row in df.iterrows():
            title = row['title']
            url = row['url']
            description = row['description']
            category_code = row['category_code']
            rss_source = RSSSource(title=title, url=url, image=None, description=description, category_id=category_code, created_at=datetime.utcnow(), updated_at=datetime.utcnow(), is_deleted=0)
            rss_sources.append(rss_source)
            # 将RSSSource对象添加到数据库会话中
        for rss_source in rss_sources:
            db.session.add(rss_source)
            # 提交RSSSource对象的变更
        db.session.commit()
        # 打印RSSSource对象
        for rss_source in rss_sources:
            print(rss_source)


